package org.goyo.common.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.DataFormatException;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.goyo.common.utils.IDcarDUtil;
import org.goyo.common.utils.SimpleMoneyFormat;

public class ExcelUtil {
    private static final Log log = LogFactory.getLog(ExcelUtil.class);
    public static final String XLS = "xls";
    public static final String XLSX = "xlsx";   

    /**
     * 导出excel 工具
     * @param filename 到处文件名称
     * @param data 数据
     * @param clazz 数据对应实体 class
     */
    public static Workbook ExcelExport(String form,List data){
        if(null==data||data.size()==0){
            return null;
        }
        if(form.equals("xls")){
            return ExcelExportHSSFWorkBook(data);
        }else if(form.equals("xlsx")){
            return ExcelExportXSSFWorkBook(data);
        }
        return null;
    }
    /**
     * 自定义数据导出excel工具
     * @param form
     * @param titles
     * @param data
     * @return
     */
    public static Workbook UserDefinedExcelExport(String form,List<String> titles,List<Object[]> data){
        if(null==data){
            return null;
        }
        if(form.equals("xls")){
            return UserDefinedExcelExportHSSFWorkBook(titles,data);
        }else if(form.equals("xlsx")){
            return UserDefinedExcelExportXSSFWorkBook(titles,data);
        }
        return null;
    }
    
    /**
     * @param titles 表头
     * @param data 表数据
     * @return
     */
    private static Workbook UserDefinedExcelExportHSSFWorkBook(
            List<String> titles, List<Object[]> data) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("sheet1");
            HSSFRow listHead = sheet.createRow(0);
            sheet.autoSizeColumn(1, true);
            listHead.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("序号");
            for (int j=0;j<titles.size();j++) {
                listHead.createCell(j+1, HSSFCell.CELL_TYPE_STRING).setCellValue(titles.get(j));
            }
            for(int i=0;i<data.size();i++){
                HSSFRow row = sheet.createRow(i+1);
                row.createCell(0).setCellValue(i+1);
                for(int k =0;k<data.get(i).length;k++){
                    if(null!=data.get(i)[k]){
                        row.createCell(k+1).setCellValue(data.get(i)[k].toString());
                    }
                }
            }
            return wb;
    } catch (Exception e) {
        log.error(e, e);
    }
    return null;
}
    /**
     * @param titles  表头
     * @param data 表数据
     * @return
     */
    private static Workbook UserDefinedExcelExportXSSFWorkBook(List<String> titles,List<Object[]> data) {
        try {
          XSSFWorkbook template = null;
          try {
              FileInputStream inputStream = new FileInputStream(ExcelUtil.class.getClassLoader().getResource("template.xlsx").getPath());
              template = new XSSFWorkbook(inputStream);
              inputStream.close();
          } catch (IOException e) {
              throw new RuntimeException(e);
          }
          SXSSFWorkbook wb = new SXSSFWorkbook(template);
          wb.setCompressTempFiles(true);
          final SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);
          sheet.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding
                                              // rows will be flushed to disk
               /* XSSFWorkbook wb = new XSSFWorkbook();
                XSSFSheet sheet = wb.createSheet("sheet1");*/
                Row listHead = sheet.createRow(0);
                sheet.autoSizeColumn(1, true);
                listHead.createCell(0, XSSFCell.CELL_TYPE_STRING).setCellValue("序号");
                for (int j=0;j<titles.size();j++) {
                    listHead.createCell(j+1, XSSFCell.CELL_TYPE_STRING).setCellValue(titles.get(j));
                }
                for(int i=0;i<data.size();i++){
                    Row row = sheet.createRow(i+1);
                    row.createCell(0).setCellValue(i+1);
                    for(int k =0;k<data.get(i).length;k++){
                        if(null!=data.get(i)[k]){
                            Cell cell = row.createCell(k+1);
                            cell.setCellValue(data.get(i)[k].toString());
                        }
                    }
                }
                return wb;
        } catch (Exception e) {
            log.error(e, e);
        }
        return null;
    }

    /**
     * @param data
     * @param clazz
     * @return
     */
    private static Workbook ExcelExportXSSFWorkBook(List data) {
        try {
               // ExcelExportClassAnnotation eca = data.get(0).getClass().getAnnotation(ExcelExportClassAnnotation.class);
                XSSFWorkbook template = null;
                try {
                  String path = java.net.URLDecoder.decode(ExcelUtil.class.getClassLoader().getResource("template.xlsx").toURI().getPath(),"utf-8");
                  log.info("template_path:"+path);
                    FileInputStream inputStream = new FileInputStream(path);
                    template = new XSSFWorkbook(inputStream);
                    inputStream.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
                SXSSFWorkbook wb = new SXSSFWorkbook(template);
                wb.setCompressTempFiles(true);
                final SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);
                sheet.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding
                                                    // rows will be flushed to disk
                Row listHead = sheet.createRow(0);
                Class one =  data.get(0).getClass();
                Field[] of = one.getDeclaredFields();
                for (Field field : of) {
                  field.setAccessible(true);
                  if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
                    ExcelImportAnnotation eea = field.getAnnotation(ExcelImportAnnotation.class);
                    listHead.createCell(Integer.parseInt(eea.column())).setCellValue(eea.columnName());
                  }else{
                      continue;
                  }
                }
                //Sheet sheet = wb.createSheet("sheet1");
               // sheet.autoSizeColumn(1, true);
               // Row  listHead = sheet.createRow(0);
                /*listHead.createCell(0, XSSFCell.CELL_TYPE_STRING).setCellValue("序号");
                for (int j=0;j<eca.listHead().length;j++) {
                    listHead.createCell(j+1, XSSFCell.CELL_TYPE_STRING).setCellValue(eca.listHead()[j]);
                }*/
               /* Class one =  data.get(0).getClass();
                Field[] of = one.getDeclaredFields();
                for (Field field : of) {
                  field.setAccessible(true);
                  if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
                    ExcelImportAnnotation eea = field.getAnnotation(ExcelImportAnnotation.class);
                    listHead.createCell(Integer.parseInt(eea.column())).setCellValue(eea.columnName());
                  }else{
                      continue;
                  }
                }*/
                for(int i=0;i<data.size();i++){
                  Row row = sheet.createRow(i+1);
                    Class obj = data.get(i).getClass();
                    Field[] fs = obj.getDeclaredFields();
                   // row.createCell(0).setCellValue(i+1);
                    for (Field field : fs) {
                        field.setAccessible(true);
                        String fieldName = field.getName();
                        if (fieldName.equalsIgnoreCase("SerialVersionUID")) {
                            continue;
                        }
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        Method[] methods = obj.getMethods();
                        Method getMethod = null;
                        for (Method method : methods) {
                            if (method.getName().equals(getMethodName)) {
                                getMethod = obj.getMethod(getMethodName,
                                        new Class[] {});
                                break;
                            }
                        }
                        Object origValue = getMethod.invoke(data.get(i), new Object[] {});
                        if(field.isAnnotationPresent(ExcelImportAnnotation.class)&&null!=origValue){
                          ExcelImportAnnotation eea = field.getAnnotation(ExcelImportAnnotation.class);
                            row.createCell(Integer.parseInt(eea.column())).setCellValue(origValue.toString());
                        }else{
                            continue;
                        }
                    }
                    if(i % 100 == 0) {  // ((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others  
                       ((SXSSFSheet) sheet).flushRows();  
                        //is a shortcut for ((SXSSFSheet)sh).flushRows(0),  
                        // this method flushes all rows  
                    }  

                }
                return wb;
        } catch (Exception e) {
            log.error(e, e);
        }
        return null;
    }

    /**
     * 导出xls
     * @param clazz 
     * @param data 
     * @return
     */
    private static Workbook ExcelExportHSSFWorkBook(List data) {
        try {
            
               // ExcelExportClassAnnotation eca = data.get(0).getClass().getAnnotation(ExcelExportClassAnnotation.class);
                
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("sheet1");
                sheet.autoSizeColumn(1, true);
                HSSFRow listHead = sheet.createRow(0);
                //listHead.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("序号");
                Class one =  data.get(0).getClass();
                Field[] of = one.getDeclaredFields();
                for (Field field : of) {
                  field.setAccessible(true);
                  if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
                    ExcelImportAnnotation eea = field.getAnnotation(ExcelImportAnnotation.class);
                    listHead.createCell(Integer.parseInt(eea.column())).setCellValue(eea.columnName());
                  }else{
                      continue;
                  }
                }
                /*for (int j=0;j<eca.listHead().length;j++) {
                    listHead.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(eca.listHead()[j]);
                }*/
                for(int i=0;i<data.size();i++){
                    HSSFRow row = sheet.createRow(i+1);
                    Class obj = data.get(i).getClass();
                    Field[] fs = obj.getDeclaredFields();
                   // row.createCell(0).setCellValue(i+1);
                    for (Field field : fs) {
                        field.setAccessible(true);
                        String fieldName = field.getName();
                        if (fieldName.equalsIgnoreCase("SerialVersionUID")) {
                            continue;
                        }
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        Method[] methods = obj.getMethods();
                        Method getMethod = null;
                        for (Method method : methods) {
                            if (method.getName().equals(getMethodName)) {
                                getMethod = obj.getMethod(getMethodName,
                                        new Class[] {});
                                break;
                            }
                        }
                        Object origValue = getMethod.invoke(data.get(i), new Object[] {});
                        if(field.isAnnotationPresent(ExcelImportAnnotation.class)&&null!=origValue){
                          ExcelImportAnnotation eea = field.getAnnotation(ExcelImportAnnotation.class);
                            row.createCell(Integer.parseInt(eea.column())).setCellValue(origValue.toString());
                        }else{
                            continue;
                        }
                    }
                }
                return wb;
        } catch (Exception e) {
            log.error(e, e);
        }
        return null;
    }

    /**
     * 导入保险数据
     * 
     * @param obj
     * @param fileBean
     *            isvertical 是否有竖向的表格
     * @return
     */
    public static Map<Object, Object> ExcelImportToObjec(Class clazz,
            byte[] bytes, boolean isvertical) {
        InputStream buffin = null;
        Map<Object, Object> result = new HashMap<Object, Object>();
        Workbook wb;
        try {
            buffin = new ByteArrayInputStream(bytes, 0, bytes.length);
            wb = WorkbookFactory.create(buffin);
            if (null != wb) {
                if (wb instanceof HSSFWorkbook) {
                    if (isvertical) {
                        return importVerticalHSSFWorkBook(wb, result, clazz);
                    } else {
                        return importHSSFWorkBook(wb, result, clazz);
                    }
                } else if (wb instanceof XSSFWorkbook) {
                    if (isvertical) {
                        return importVerticalXSSFWorkBook(wb, result, clazz);
                    } else {
                        return importXSSFWorkBook(wb, result, clazz);
                    }
                } else {
                    result.clear();
                    result.put(false, "无法识别文件格式！");
                    return result;
                }
            } else {
                result.clear();
                result.put(false, "无法识别文件格式！");
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.clear();
            result.put(false, "无法识别文件格式！");
            return result;
        } finally {
            if (null != buffin) {
                try {
                    buffin.close();
                } catch (IOException e) {
                    log.error(e, e);
                }
            }
        }
    }

    /**
     * @param wb
     * @param result
     * @param clazz
     * @return
     */
    private static Map<Object, Object> importVerticalXSSFWorkBook(Workbook wb,
            Map<Object, Object> result, Class clazz) {
        try {
            Object tempObj = clazz.newInstance();// 临时 用于获得注解的 数据
            ExcelImportClassAnnotation eca = tempObj.getClass().getAnnotation(
                    ExcelImportClassAnnotation.class);
            String[] VerticalKey = null;
            if (tempObj.getClass().isAnnotationPresent(
                    ExcelImportVerticalKeyClassAnnotation.class)) {
                ExcelImportVerticalKeyClassAnnotation evkc = tempObj.getClass()
                        .getAnnotation(
                                ExcelImportVerticalKeyClassAnnotation.class);
                VerticalKey = evkc.key();
            }
            List<Object> list = new ArrayList<Object>();
            for (int sh = 0; sh < eca.sheetNum().length; sh++) {
                String sheetn = eca.sheetNum()[sh];
                Boolean ableNull = null;
                try {
                    ableNull = eca.sheetAbleNull()[sh];
                } catch (Exception e) {
                    result.clear();
                    result.put(false, "导入实体配置错误，导入失败！");
                    return result;
                }
                log.info("此文件中表的数量是:------" + wb.getNumberOfSheets());
                XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(Integer
                        .parseInt(sheetn)); // 获取当前文件的sheet1

                Integer cKey = null;// 处理特殊列 竖向转横向数据列标
                if (null != VerticalKey && null != VerticalKey[sh]) {
                    try {
                        cKey = Integer.parseInt(VerticalKey[sh]);
                    } catch (Exception e) {
                        cKey = null;
                    }

                }
                // 取得有效的行数 下标是从0开始的
                int rowcount = sheet.getLastRowNum();
                if (rowcount >= Integer.parseInt(eca.row())) {
                    for (int i = Integer.parseInt(eca.row()); i <= rowcount; i++) {
                        Object c = clazz.newInstance();
                        XSSFRow row = sheet.getRow(i);// 获取行对象 下标是从1开始的
                        String keyCellValue = null;
                        if (null != cKey) {
                            XSSFCell keyCell = row.getCell(cKey);
                            keyCellValue = getXSSFCellValue(keyCell,null,false,null);
                        }

                        // int colcount = row.getLastCellNum();// 获得当前行有多少单元格
                        // 下标也是从1开始
                        Field[] fs = c.getClass().getDeclaredFields();
                        for (Field f : fs) {
                            f.setAccessible(true);
                            String fieldName = f.getName();
                            if (f.isAnnotationPresent(ExcelImportAnnotation.class)
                                    || f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                if (fieldName
                                        .equalsIgnoreCase("SerialVersionUID")) {
                                    continue;
                                }
                                Integer columnNum = null;//列
                                boolean isDate = false;//是否为日期格式
                                String dateForm = null;//时间格式
                                String parameterType = null;//参数类型
                                Authentication authentication = null;
                                boolean returnsString = false;
                                boolean nullAble = true;
                                if (sh == 0) {
                                    if (!f.isAnnotationPresent(ExcelImportAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportAnnotation ea = f
                                            .getAnnotation(ExcelImportAnnotation.class);
                                    columnNum = Integer.parseInt(ea.column());
                                    isDate = ea.isDate();
                                    dateForm = ea.dateForm();
                                    parameterType = ea.parameterType();
                                    authentication = ea.authentication();
                                    returnsString = ea.returnsString();
                                    nullAble = ea.nullAble();
                                } else {
                                    if (!f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportMultilistSheetAnnotation emsa = f
                                            .getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                                    columnNum = Integer.parseInt(emsa.column());
                                    isDate = emsa.isDate();
                                    dateForm = emsa.dateForm();
                                    parameterType = emsa.parameterType();
                                    authentication = emsa.authentication();
                                    returnsString = emsa.returnsString();
                                    nullAble = emsa.nullAble();
                                }
                                String setMethodName = "set"
                                        + fieldName.substring(0, 1)
                                                .toUpperCase()
                                        + fieldName.substring(1);
                                Method[] methods = clazz.getMethods();

                                Method setMethod = null;
                                for (Method method : methods) {
                                    if (method.getName().equals(setMethodName)) {
                                        setMethod = clazz.getMethod(
                                                setMethodName,
                                                new Class[] { f.getType() });
                                        break;
                                    }
                                }
                                if (null == setMethod) {
                                    result.clear();
                                    result.put(false, "系统错误，导入失败！");
                                    return result;
                                }
                                /*XSSFCell cell = row.getCell(columnNum);
                                if(null==cell){
                                    continue;
                                }*/
                                String errorMsg= "";
                                try {
                                  XSSFCell cell = row.getCell(columnNum);//下标从1开始
                                  if(null==cell){
                                    if(!nullAble){
                                      errorMsg= "此列数据不能为空！";
                                      throw new Exception("此列数据不能为空！");
                                    }else{
                                      continue;
                                    }
                                  }
                                    String cellValue = getXSSFCellValue(cell,dateForm,isDate,authentication);
                                    if(!nullAble){
                                        if(StringUtils.isEmpty(cellValue)){
                                            errorMsg= "此列数据不能为空！";
                                            throw new Exception("此列数据不能为空！");
                                        }
                                    }
                                    if (!isDate) {
                                        String coderesult = null;
                                        if (cKey == columnNum) {
                                            coderesult = getXSSFVerticalToCrosswiseValue(
                                                    f, keyCellValue, row,sh);
                                        } else {
                                            coderesult = getconversionType(f,
                                                    cellValue);
                                        }
                                        if (null != coderesult) {
                                            Object resutValue =  typeConversion(coderesult,parameterType,returnsString);
                                            setMethod.invoke(c, resutValue);
                                        }
                                    } else {
                                        setMethod.invoke(c, cellValue);
                                    }
                                } catch (Exception e) {
                                    e.printStackTrace();
                                    result.clear();
                                    result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表第" + (i+1) + " 行的第 "
                                            + (columnNum+1) + " 列格式错误 "+errorMsg+" !");
                                    return result;
                                }
                            }
                        }
                        list.add(c);
                    }
                    result.put(true, list);
                } else {
                    if(!ableNull){
                        result.clear();
                        result.put(false, "表"+(Integer.parseInt(sheetn)+1)+"导入数据不能为空，导入失败！");
                        return result;
                    }
                    /*if(eca.sheetNum().length==1||(eca.sheetNum().length>1&&sh==0)){
                        result.clear();
                        result.put(false, "导入数据为空！");
                        return result;
                    }*/
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.clear();
            result.put(false, "系统错误，导入失败！");
            return result;
        }
        return result;
    }

    /**
     * 高版本文件
     * 
     * @param f
     * @param keyCellValue
     * @param row
     * @return
     * @throws ParseException 
     */
    private static String getXSSFVerticalToCrosswiseValue(Field f,
            String keyCellValue, XSSFRow row,int sh) throws ParseException {
        Integer targetColumn = null;
        String code = null;
        if (sh==0&&f.isAnnotationPresent(ExcelImportVerticalHeadOfTheCostValueAnnotation.class)) {
            ExcelImportVerticalHeadOfTheCostValueAnnotation evhca = f
                    .getAnnotation(ExcelImportVerticalHeadOfTheCostValueAnnotation.class);
            try {
                targetColumn = Integer.parseInt(evhca.tragetColumn());
                code = evhca.code();
            } catch (Exception e) {
                log.error("配置错误", e);
                targetColumn = null;
                code = null;
            }
        } else if (f
                .isAnnotationPresent(ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation.class)) {
            ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation emvhca = f
                    .getAnnotation(ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation.class);
            try {
                targetColumn = Integer.parseInt(emvhca.tragetColumn());
                code = emvhca.code();
            } catch (Exception e) {
                log.error("配置错误", e);
                targetColumn = null;
                code = null;
            }
        }
        if (null != targetColumn && null != code) {
            String targetValue = getXSSFCellValue(row.getCell(targetColumn),null,false,null);
            if (keyCellValue.trim().equals("单位缴统筹") && code.equals("01")) {
                return targetValue;
            } else if (keyCellValue.trim().equals("个人缴纳") && code.equals("00")) {
                return targetValue;
            }
        }
        return null;
    }

    /**
     * 高版本文件 2007
     * @param dateForm 
     * @param authentication 
     * 
     * @param keyCell
     * @return
     * @throws ParseException 
     */
    private static String getXSSFCellValue(XSSFCell cell, String dateForm,boolean isDate, Authentication authentication) throws ParseException {
        String result = null;
        if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            try {
                if(isDate){
                    Date d = cell.getDateCellValue();
                    if(null==dateForm){
                        dateForm = "yyyyMM";
                    }
                    SimpleDateFormat sf = new SimpleDateFormat(dateForm);
                    result = sf.format(d);
                }else{
                    //String whatYourWant = df.format(cell.getNumericCellValue()); 
                    double whatYourWant = cell.getNumericCellValue();
                    result = SimpleMoneyFormat.convertScienceNum(whatYourWant) + "";
                }
            } catch (Exception e) {
                //String whatYourWant = df.format(cell.getNumericCellValue()); 
                String whatYourWant = SimpleMoneyFormat.convertScienceNum(cell.getNumericCellValue());
                result = whatYourWant + "";
            }
        } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
            result = null;
        }  else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
            result = cell.getStringCellValue();
            if(null!=result&&result.length()==0){
                result = null;
            }
        } else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
            result = SimpleMoneyFormat.convertScienceNum(cell.getNumericCellValue()) + "";
        }else {
            try {
                Date d = cell.getDateCellValue();
                SimpleDateFormat sf = new SimpleDateFormat("yyyyMM");
                result = sf.format(d);
            } catch (Exception e) {
                log.error("格式无法识别", e);
            }
        }
        if(null!=authentication&&!authentication.equals(Authentication.NULL)){
            if(authentication.equals(Authentication.IDCARD)){
                String rr = IDcarDUtil.IDCardValidate(result.trim());
                if(!rr.equals("")){
                    throw new ParseException(rr, 0);
                }
            }
        }
        return result;
    }

    /**
     * 失业保险特殊处理方式
     * 
     * @param wb
     * @param result
     * @param clazz
     * @return
     */
    private static Map<Object, Object> importVerticalHSSFWorkBook(Workbook wb,
            Map<Object, Object> result, Class clazz) {
        try {
            Object tempObj = clazz.newInstance();// 临时 用于获得注解的 数据
            ExcelImportClassAnnotation eca = tempObj.getClass().getAnnotation(
                    ExcelImportClassAnnotation.class);
            String[] VerticalKey = null;
            if (tempObj.getClass().isAnnotationPresent(
                    ExcelImportVerticalKeyClassAnnotation.class)) {
                ExcelImportVerticalKeyClassAnnotation evkc = tempObj.getClass()
                        .getAnnotation(
                                ExcelImportVerticalKeyClassAnnotation.class);
                VerticalKey = evkc.key();
            }
            List<Object> list = new ArrayList<Object>();
            for (int sh = 0; sh < eca.sheetNum().length; sh++) {
                String sheetn = eca.sheetNum()[sh];
                Boolean ableNull = null;
                try {
                    ableNull = eca.sheetAbleNull()[sh];
                } catch (Exception e) {
                    result.clear();
                    result.put(false, "导入实体配置错误，导入失败！");
                    return result;
                }
                log.info("此文件中表的数量是:------" + wb.getNumberOfSheets());
                HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(Integer
                        .parseInt(sheetn)); // 获取当前文件的sheet1

                Integer cKey = null;// 处理特殊列 竖向转横向数据列标
                if (null != VerticalKey && null != VerticalKey[sh]) {
                    try {
                        cKey = Integer.parseInt(VerticalKey[sh]);
                    } catch (Exception e) {
                        cKey = null;
                    }

                }
                // 取得有效的行数 下标是从0开始的
                int rowcount = sheet.getLastRowNum();
                if (rowcount >= Integer.parseInt(eca.row())) {
                    for (int i = Integer.parseInt(eca.row()); i <= rowcount; i++) {
                        Object c = clazz.newInstance();
                        HSSFRow row = sheet.getRow(i);// 获取行对象 下标是从1开始的
                        String keyCellValue = null;
                        if (null != cKey) {
                            HSSFCell keyCell = row.getCell(cKey);
                            keyCellValue = getCellValue(keyCell,null,false,null);
                        }

                        // int colcount = row.getLastCellNum();// 获得当前行有多少单元格
                        // 下标也是从1开始
                        Field[] fs = c.getClass().getDeclaredFields();
                        for (Field f : fs) {
                            f.setAccessible(true);
                            String fieldName = f.getName();
                            if (f.isAnnotationPresent(ExcelImportAnnotation.class)
                                    || f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                if (fieldName
                                        .equalsIgnoreCase("SerialVersionUID")) {
                                    continue;
                                }
                                Integer columnNum = null;
                                boolean isDate = false;
                                String dateForm = null;
                                String parameterType = null;
                                Authentication authentication = null;
                                boolean returnsString = false;
                                boolean nullAble = true;
                                if (sh == 0) {
                                    if (!f.isAnnotationPresent(ExcelImportAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportAnnotation ea = f
                                            .getAnnotation(ExcelImportAnnotation.class);
                                    columnNum = Integer.parseInt(ea.column());
                                    isDate = ea.isDate();
                                    dateForm = ea.dateForm();
                                    parameterType = ea.parameterType();
                                    authentication = ea.authentication();
                                    returnsString = ea.returnsString();
                                    nullAble = ea.nullAble();
                                } else {
                                    if (!f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportMultilistSheetAnnotation emsa = f
                                            .getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                                    columnNum = Integer.parseInt(emsa.column());
                                    isDate = emsa.isDate();
                                    dateForm = emsa.dateForm();
                                    parameterType = emsa.parameterType();
                                    authentication = emsa.authentication();
                                    returnsString = emsa.returnsString();
                                    nullAble = emsa.nullAble();
                                }
                                String setMethodName = "set"
                                        + fieldName.substring(0, 1)
                                                .toUpperCase()
                                        + fieldName.substring(1);
                                Method[] methods = clazz.getMethods();

                                Method setMethod = null;
                                for (Method method : methods) {
                                    if (method.getName().equals(setMethodName)) {
                                        setMethod = clazz.getMethod(
                                                setMethodName,
                                                new Class[] { f.getType() });
                                        break;
                                    }
                                }
                                if (null == setMethod) {
                                    result.clear();
                                    result.put(false, "系统错误，导入失败！");
                                    return result;
                                }
                              /*  HSSFCell cell = row.getCell(columnNum);
                                if(null==cell){
                                    continue;
                                }*/
                                String errorMsg="";//错误信息 暂用 后期优化
                                try {
                                  HSSFCell cell = row.getCell(columnNum);//下标从1开始
                                  if(null==cell){
                                    if(!nullAble){
                                      errorMsg= "此列数据不能为空！";
                                      throw new Exception("此列数据不能为空！");
                                    }else{
                                      continue;
                                    }
                                  }
                                    String cellValue = getCellValue(cell,dateForm,isDate,authentication);
                                    if(!nullAble){
                                        if(StringUtils.isEmpty(cellValue)){
                                            errorMsg= "此列数据不能为空！";
                                            throw new Exception("此列数据不能为空！");
                                        }
                                    }
                                    if (!isDate) {
                                        String coderesult = null;
                                        if (cKey == columnNum) {
                                            coderesult = getVerticalToCrosswiseValue(
                                                    f, keyCellValue, row,sh);
                                        } else {
                                            coderesult = getconversionType(f,
                                                    cellValue);
                                        }
                                        if (null != coderesult) {
                                            Object resutValue =  typeConversion(coderesult,parameterType,returnsString);
                                            setMethod.invoke(c, resutValue);
                                        }
                                    } else {
                                        setMethod.invoke(c, cellValue);
                                    }
                                } catch (Exception e) {
                                    log.error(e, e);
                                    result.clear();
                                    result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表 第 " + (i+1) + " 行的第 "
                                            + (columnNum+1) + " 列格式错误 "+errorMsg+" !");
                                    return result;
                                }
                            }
                        }
                        list.add(c);
                    }
                    result.put(true, list);
                } else {
                    if(!ableNull){
                        result.clear();
                        result.put(false, "表"+(Integer.parseInt(sheetn)+1)+"导入数据不能为空，导入失败！");
                        return result;
                    }
                    /*if(eca.sheetNum().length==1||(eca.sheetNum().length>1&&sh==0)){
                        result.clear();
                        result.put(false, "导入数据为空！");
                        return result;
                    }*/
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.clear();
            result.put(false, "系统错误，导入失败！");
            return result;
        }
        return result;
    }

    /**
     * 类型转换
     * @param coderesult
     * @param parameterType
     * @param returnsString 
     * @return
     */
    private static Object typeConversion(String coderesult, String parameterType, boolean returnsString) throws ClassCastException {
        Object result = coderesult;
        try {
            if(null!=parameterType){
                if(parameterType.equals("Integer")||parameterType.equals("int")){
                    result= Integer.parseInt(coderesult);
                }else if(parameterType.equals("String")){
                    result= coderesult;
                }else if(parameterType.equals("Double")||parameterType.equals("double")){
                    result= Double.parseDouble(coderesult);
                }else if(parameterType.equals("Long")||parameterType.equals("long")){
                    result= Long.parseLong(coderesult);
                }
            }else{
                return result;
            }
        } catch (ClassCastException e) {
            throw new ClassCastException("类型转换错误！");
        }
        if(returnsString){//绝对返回字符串
            result = result + "";
        }
        return result;
    }
    /**
     * 处理竖向变横向数据
     * 
     * @param f
     * @param row
     * @param sh 
     * @param cellValue
     * @return
     * @throws ParseException 
     */
    private static String getVerticalToCrosswiseValue(Field f,
            String keyCellValue, HSSFRow row, int sh) throws ParseException {
        Integer targetColumn = null;
        String code = null;
        if (sh==0&&f.isAnnotationPresent(ExcelImportVerticalHeadOfTheCostValueAnnotation.class)) {
            ExcelImportVerticalHeadOfTheCostValueAnnotation evhca = f
                    .getAnnotation(ExcelImportVerticalHeadOfTheCostValueAnnotation.class);
            try {
                targetColumn = Integer.parseInt(evhca.tragetColumn());
                code = evhca.code();
            } catch (Exception e) {
                log.error("配置错误", e);
                targetColumn = null;
                code = null;
            }
        } else if (f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)&&f
                .isAnnotationPresent(ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation.class)) {
            ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation emvhca = f
                    .getAnnotation(ExcelImportMultilisteVerticalHeadOfTheCostValueAnnotation.class);
            try {
                targetColumn = Integer.parseInt(emvhca.tragetColumn());
                code = emvhca.code();
            } catch (Exception e) {
                log.error("配置错误", e);
                targetColumn = null;
                code = null;
            }
        }
        if (null != targetColumn && null != code) {
            String targetValue = getCellValue(row.getCell(targetColumn),null,false,null);
            if (keyCellValue.trim().equals("单位缴统筹") && code.equals("01")) {
                return targetValue;
            } else if (keyCellValue.trim().equals("个人缴纳") && code.equals("00")) {
                return targetValue;
            }
        }
        return null;
    }

    /**
     * 字典表对应转换
     * 
     * @param f
     * @param cellValue
     * @return
     * @throws Exception 
     */
    private static String getconversionType(Field f, String cellValue) throws Exception {
        if(null==cellValue){
            return null;
        }
        String codeResult = cellValue;
        if (f.isAnnotationPresent(ExcelImportPayMentTypeAnnotation.class)) {
            if (cellValue.trim().contains("正常")) {
                codeResult = "00";
            } else if (cellValue.trim().equals("差额补退")) {
                codeResult = "01";
            } else if (cellValue.trim().equals("差额补收")) {
                codeResult = "02";
            } else if (cellValue.trim().equals("差额退收")) {
                codeResult = "03";
            } else if (cellValue.trim().contains("补退")) {
                codeResult = "01";
            } else if (cellValue.trim().contains("补收")) {
                codeResult = "02";
            } else if (cellValue.trim().contains("退收")) {
                codeResult = "03";
            }else{
                throw new DataFormatException("数据错误");
            }
        } else if (f
                .isAnnotationPresent(ExcelImportMedicalInsuranceCourseAnnotation.class)) {
            if (cellValue.trim().equals("基本医疗保险")) {
                codeResult = "00";
            } else if (cellValue.trim().equals("大额医疗保险")) {
                codeResult = "01";
            }else{
                throw new DataFormatException("数据错误");
            }
        } else if (f
                .isAnnotationPresent(ExcelImportCaptureExpendsLogoAnnotation.class)) {
            if (cellValue.trim().equals("欠缴")) {
                codeResult = "00";
            }
        } else if (f
                .isAnnotationPresent(ExcelImportPaymentMethodAnnotation.class)) {
            if (cellValue.trim().contains("个人")) {
                codeResult = "00";
            } else if (cellValue.trim().equals("单位")) {
                codeResult = "01";
            } else if (cellValue.trim().equals("单位代扣")) {
                codeResult = "02";
            }else{
                throw new DataFormatException("数据错误");
            }
        } else if (f.isAnnotationPresent(ExcelimportCertificateTypeAnnotation.class)){
            if(cellValue.trim().contains("身份证")){
                codeResult = "00";
            } else if (cellValue.trim().contains("驾驶证")){
                codeResult = "01";
            }else{
                throw new DataFormatException("数据错误");
            }
        } else if(f.isAnnotationPresent(ExcelimportResidentAccountTypeAnnotation.class)){
            if(cellValue.trim().contains("农村")){
                codeResult = "countryside";
            } else if (cellValue.trim().contains("城镇")){
                codeResult = "town";
            }else{
                throw new DataFormatException("数据错误");
            }
        }else if(f.isAnnotationPresent(ExcelimportAttendsInsuranceStatusAnnotation.class)){
            if(cellValue.trim().contains("正常参保")){
                codeResult = "00";
            } else if (cellValue.trim().contains("未参保")){
                codeResult = "01";
            }else if(cellValue.trim().contains("暂停参保")){
                codeResult = "02";
            }else{
                throw new DataFormatException("数据错误");
            }
        }
        return codeResult;
    }

    /**
     * 获得单元格数据 2003
     * 
     * @param cell
     * @return
     */
    private static final DecimalFormat df = new DecimalFormat("0");
    private static String getCellValue(HSSFCell cell, String dateForm, boolean isDate, Authentication authentication) throws ParseException {
        String result = null;
        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)&&isDate) {// 时间类型
                Date d = cell.getDateCellValue();
                if(null==dateForm){
                    dateForm = "yyyyMM";
                }
                SimpleDateFormat sf = new SimpleDateFormat(dateForm);
                result = sf.format(d);
            } else {
                //String whatYourWant = df.format(cell.getNumericCellValue());  
                double whatYourWant = cell.getNumericCellValue();  
                result = SimpleMoneyFormat.convertScienceNum(whatYourWant) + "";
            }
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            result = null;
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            result = cell.getStringCellValue();
            if(null!=result&&result.length()==0){
                result = null;
            }
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
            result = SimpleMoneyFormat.convertScienceNum(cell.getNumericCellValue()) + "";
        }
        if(null!=authentication&&!authentication.equals(Authentication.NULL)){
            if(authentication.equals(Authentication.IDCARD)){
                String rr = IDcarDUtil.IDCardValidate(result.trim());
                if(!rr.equals("")){
                    throw new ParseException(rr, 0);
                }
            }
        }
        return result;
    }

    /**
     * 操作office2010
     * 
     * @param wb
     * @param result
     * @param obj
     */
    private static Map<Object, Object> importXSSFWorkBook(Workbook wb,
            Map<Object, Object> result, Class clazz) {
        try {
            Object tempObj = clazz.newInstance();// 临时 用于获得注解的 数据
            ExcelImportClassAnnotation eca = tempObj.getClass().getAnnotation(
                    ExcelImportClassAnnotation.class);
            List<Object> list = new ArrayList<Object>();
            for (int sh = 0; sh < eca.sheetNum().length; sh++) {
                String sheetn = eca.sheetNum()[sh];
                Boolean ableNull = null;
                try {
                    ableNull = eca.sheetAbleNull()[sh];
                } catch (Exception e) {
                    result.clear();
                    result.put(false, "导入实体配置错误，导入失败！");
                    return result;
                }
                log.info("此文件中表的数量是:------" + wb.getNumberOfSheets());
                XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(Integer
                        .parseInt(sheetn)); // 获取当前文件的sheet1
                // 取得有效的行数 下标是从0开始的
                int rowcount = sheet.getLastRowNum();
                try {
                  if(eca.authenticationHeader()){
                    XSSFRow row = sheet.getRow(0);
                    Field[] tmpfs = tempObj.getClass().getDeclaredFields();
                    for (Field field : tmpfs) {
                      field.setAccessible(true);
                      if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
                        ExcelImportAnnotation eia  = field.getAnnotation(ExcelImportAnnotation.class);
                        if(!(eia.columnName().trim().equals(row.getCell(Integer.parseInt(eia.column())).getStringCellValue().trim()))){
                          result.clear();
                          result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,第 0行的第 "
                              + (Integer.parseInt(eia.column())+1) + "列 不符合模板要求");
                          return result;
                        }
                      }else if(field.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)){
                        ExcelImportMultilistSheetAnnotation eia  = field.getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                        if(!(eia.columnName().trim().equals(row.getCell(Integer.parseInt(eia.column())).getStringCellValue().trim()))){
                          result.clear();
                          result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,第 0行的第 "
                              + (Integer.parseInt(eia.column()+1)) + "列 不符合模板要求");
                          return result;
                        }
                      }
                    }
                  }
                } catch (Exception e) {
                  result.clear();
                  result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,表头不符合模板要求 ");
                  return result;
                }
                // log.error("此表中的有效行数为:------" + (rowcount+1));
                if (rowcount >= Integer.parseInt(eca.row())) {
                    for (int i = Integer.parseInt(eca.row()); i <= rowcount; i++) {
                        Object c = clazz.newInstance();
                        XSSFRow row = sheet.getRow(i);// 获取行对象 下标是从1开始的
                        int colcount = row.getLastCellNum();// 获得当前行有多少单元格
                                                            // 下标也是从1开始
                        Field[] fs = c.getClass().getDeclaredFields();
                        for (Field f : fs) {
                            f.setAccessible(true);
                            String fieldName = f.getName();
                            if (f.isAnnotationPresent(ExcelImportAnnotation.class)
                                    || f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                if (fieldName
                                        .equalsIgnoreCase("SerialVersionUID")) {
                                    continue;
                                }
                                Integer columnNum = null;
                                boolean isDate = false;
                                String dateForm = null;
                                String parameterType = null;
                                Authentication authentication = null;
                                boolean returnsString = false;
                                boolean nullAble =true;
                                if (sh == 0) {
                                    if (!f.isAnnotationPresent(ExcelImportAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportAnnotation ea = f
                                            .getAnnotation(ExcelImportAnnotation.class);
                                    columnNum = Integer.parseInt(ea.column());
                                    isDate = ea.isDate();
                                    dateForm = ea.dateForm();
                                    parameterType = ea.parameterType();
                                    authentication =ea.authentication();
                                    returnsString = ea.returnsString();
                                    nullAble = ea.nullAble();
                                } else {
                                    if (!f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportMultilistSheetAnnotation emsa = f
                                            .getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                                    columnNum = Integer.parseInt(emsa.column());
                                    isDate = emsa.isDate();
                                    dateForm = emsa.dateForm();
                                    parameterType = emsa.parameterType();
                                    authentication =emsa.authentication();
                                    returnsString = emsa.returnsString();
                                    nullAble = emsa.nullAble();
                                }
                                String setMethodName = "set"
                                        + fieldName.substring(0, 1)
                                                .toUpperCase()
                                        + fieldName.substring(1);
                                Method[] methods = clazz.getMethods();

                                Method setMethod = null;
                                for (Method method : methods) {
                                    if (method.getName().equals(setMethodName)) {
                                        setMethod = clazz.getMethod(
                                                setMethodName,
                                                new Class[] { f.getType() });
                                        break;
                                    }
                                }
                                if (null == setMethod) {
                                    result.clear();
                                    result.put(false, "系统错误导入失败!");
                                    return result;
                                }
                              
                                String errorMsg="";
                                try {
                                  XSSFCell cell = row.getCell(columnNum);//下标从1开始
                                  if(null==cell){
                                    if(!nullAble){
                                      errorMsg= "此列数据不能为空！";
                                      throw new Exception("此列数据不能为空！");
                                    }else{
                                      continue;
                                    }
                                  }
                                    String cellValue = getXSSFCellValue(cell,dateForm,isDate,authentication);
                                    if(!nullAble){
                                        if(StringUtils.isEmpty(cellValue)){
                                            errorMsg= "此列数据不能为空！";
                                            throw new Exception("此列数据不能为空！");
                                        }
                                    }
                                    if (!isDate) {
                                        String conversion = getconversionType(f, cellValue);
                                        if(null!=conversion){
                                            Object resutValue =  typeConversion(conversion,parameterType,returnsString);
                                            setMethod.invoke(c, resutValue);
                                        }
                                    } else {
                                        setMethod.invoke(c, cellValue);
                                    }
                                } catch (Exception e) {
                                    e.printStackTrace();
                                    result.clear();
                                    result.put(false, "导入失败：第"+(Integer.parseInt(sheetn)+1)+"张表第 " + (i+1) + " 行的第 "
                                            + (columnNum+1) + " 列格式错误 "+errorMsg+" !");
                                    return result;
                                }
                            }
                        }
                        list.add(c);
                    }
                    result.put(true, list);
                } else {
                    if(!ableNull){
                        result.clear();
                        result.put(false, "表"+(Integer.parseInt(sheetn)+1)+"导入数据不能为空，导入失败！");
                        return result;
                    }
                    /*if(eca.sheetNum().length==1||(eca.sheetNum().length>1&&sh==0)){
                        result.clear();
                        result.put(false, "导入数据为空！");
                        return result;
                    }*/
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            result.clear();
            result.put(false, "系统错误,导入失败!");
            return result;
        }
        return result;
    }

    /**
     * 操作office2003
     * 
     * @param wb
     * @param result
     * @param obj
     */
    private static Map<Object, Object> importHSSFWorkBook(Workbook wb,
            Map<Object, Object> result, Class clazz) {
        try {
            Object tempObj = clazz.newInstance();// 临时 用于获得注解的 数据
            ExcelImportClassAnnotation eca = tempObj.getClass().getAnnotation(
                    ExcelImportClassAnnotation.class);
            List<Object> list = new ArrayList<Object>();
            for (int sh = 0; sh < eca.sheetNum().length; sh++) {
                String sheetn = eca.sheetNum()[sh];
                Boolean ableNull = null;
                try {
                    ableNull = eca.sheetAbleNull()[sh];
                } catch (Exception e) {
                    result.clear();
                    result.put(false, "导入实体配置错误，导入失败！");
                    return result;
                }
                log.info("此文件中表的数量是:------" + wb.getNumberOfSheets());
                HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(Integer
                        .parseInt(sheetn)); // 获取当前文件的sheet1
                // 取得有效的行数 下标是从0开始的
                int rowcount = sheet.getLastRowNum();
                try {
                  if(eca.authenticationHeader()){
                    HSSFRow row = sheet.getRow(0);
                    Field[] tmpfs = tempObj.getClass().getDeclaredFields();
                    for (Field field : tmpfs) {
                      field.setAccessible(true);
                      if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
                        ExcelImportAnnotation eia  = field.getAnnotation(ExcelImportAnnotation.class);
                        if(!(eia.columnName().equals(row.getCell(Integer.parseInt(eia.column())).getStringCellValue()))){
                          result.clear();
                          result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,第 0行的第 "
                              + (eia.column()+1) + "列 不符合模板要求");
                          return result;
                        }
                      }else if(field.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)){
                        ExcelImportMultilistSheetAnnotation eia  = field.getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                        if(!(eia.columnName().equals(row.getCell(Integer.parseInt(eia.column())).getStringCellValue()))){
                          result.clear();
                          result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,第 0行的第 "
                              + (eia.column()+1) + "列 不符合模板要求");
                          return result;
                        }
                      }
                    }
                  }
                } catch (Exception e) {
                  result.clear();
                  result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表,表头不符合模板要求 ");
                  return result;
                }
                
                
                if (rowcount >= Integer.parseInt(eca.row())) {
                    for (int i = Integer.parseInt(eca.row()); i <= rowcount; i++) {
                        Object c = clazz.newInstance();
                        HSSFRow row = sheet.getRow(i);// 获取行对象 下标是从1开始的
                        // int colcount = row.getLastCellNum();// 获得当前行有多少单元格
                        // 下标也是从1开始
                        Field[] fs = c.getClass().getDeclaredFields();
                        for (Field f : fs) {
                            f.setAccessible(true);
                            String fieldName = f.getName();
                            if (f.isAnnotationPresent(ExcelImportAnnotation.class)
                                    || f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                if (fieldName
                                        .equalsIgnoreCase("SerialVersionUID")) {
                                    continue;
                                }
                                Integer columnNum = null;
                                boolean isDate = false;
                                String dateForm = null;
                                String parameterType = null;
                                Authentication authentication = null;
                                boolean returnsString = false;
                                boolean nullAble = true;
                                if (sh == 0) {
                                    if (!f.isAnnotationPresent(ExcelImportAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportAnnotation ea = f
                                            .getAnnotation(ExcelImportAnnotation.class);
                                    columnNum = Integer.parseInt(ea.column());
                                    isDate = ea.isDate();
                                    dateForm = ea.dateForm();
                                    parameterType = ea.parameterType();
                                    authentication = ea.authentication();
                                    returnsString = ea.returnsString();
                                    nullAble =ea.nullAble();
                                } else {
                                    if (!f.isAnnotationPresent(ExcelImportMultilistSheetAnnotation.class)) {
                                        continue;
                                    }
                                    ExcelImportMultilistSheetAnnotation emsa = f
                                            .getAnnotation(ExcelImportMultilistSheetAnnotation.class);
                                    columnNum = Integer.parseInt(emsa.column());
                                    isDate = emsa.isDate();
                                    dateForm = emsa.dateForm();
                                    parameterType = emsa.parameterType();
                                    authentication = emsa.authentication();
                                    returnsString = emsa.returnsString();
                                    nullAble =emsa.nullAble();
                                }
                                String setMethodName = "set"
                                        + fieldName.substring(0, 1)
                                                .toUpperCase()
                                        + fieldName.substring(1);
                                Method[] methods = clazz.getMethods();

                                Method setMethod = null;
                                for (Method method : methods) {
                                    if (method.getName().equals(setMethodName)) {
                                        setMethod = clazz.getMethod(
                                                setMethodName,
                                                new Class[] { f.getType() });
                                        break;
                                    }
                                }
                                if (null == setMethod) {
                                    result.clear();
                                    result.put(false, "系统错误，导入失败！");
                                    return result;
                                }
                                /*HSSFCell cell = row.getCell(columnNum);//下标是从0开始
                                if(null==cell){
                                    continue;
                                }*/
                                String errorMsg = "";
                                try {
                                  HSSFCell cell = row.getCell(columnNum);//下标从1开始
                                  if(null==cell){
                                    if(!nullAble){
                                      errorMsg= "此列数据不能为空！";
                                      throw new Exception("此列数据不能为空！");
                                    }else{
                                      continue;
                                    }
                                  }
                                    String cellValue = getCellValue(cell,dateForm,isDate,authentication);
                                    if(!nullAble){
                                        if(StringUtils.isEmpty(cellValue)){
                                            errorMsg= "此列数据不能为空！";
                                            throw new Exception("此列数据不能为空！");
                                        }
                                    }
                                    if (!isDate) {
                                        String conversion = getconversionType(f, cellValue);
                                        if(null!=conversion){
                                            Object resutValue =  typeConversion(conversion,parameterType,returnsString);
                                            setMethod.invoke(c, resutValue);
                                        }
                                    } else {
                                        setMethod.invoke(c, cellValue);
                                    }
                                } catch (Exception e) {
                                    log.error(e, e);
                                    result.clear();
                                    result.put(false, "导入失败：第 "+(Integer.parseInt(sheetn)+1)+"张表第" + (i+1)+ " 行的第 "
                                            + (columnNum+1) + " 列格式错误 "+errorMsg+ "!");
                                    return result;
                                }
                            }
                        }
                        list.add(c);
                    }
                    result.put(true, list);
                } else {
                    if(!ableNull){
                        result.clear();
                        result.put(false, "表"+(Integer.parseInt(sheetn)+1)+"导入数据不能为空，导入失败！");
                        return result;
                    }
                    /*if(eca.sheetNum().length==1||(eca.sheetNum().length>1&&sh==0)){
                        result.clear();
                        result.put(false, "导入数据为空！");
                        return result;
                    }*/
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.clear();
            result.put(false, "系统错误，导入失败！");
            return result;
        }
        return result;
    }
    
    
    
    public static byte[] getBytes(InputStream is) throws IOException {
      ByteArrayOutputStream buffer = new ByteArrayOutputStream();

      int len;
      byte[] data = new byte[100000];
      while ((len = is.read(data, 0, data.length)) != -1) {
      buffer.write(data, 0, len);
      }

      buffer.flush();
      return buffer.toByteArray();
  }
}
